Skip to main content

Local Disk Cache in Snowflake


πŸ”Ή What is Local Disk Cache?​

When a Snowflake virtual warehouse runs a query, it needs to read data from cloud storage (Amazon S3, Azure Blob, or Google Cloud Storage). To avoid repeatedly pulling the same data from storage, Snowflake keeps a local cache of data blocks on the SSD disks of the warehouse nodes.

πŸ‘‰ This is called Local Disk Cache (sometimes also referred to as data cache).

It stores raw micro-partition data that the warehouse has recently accessed.


πŸ”Ή Why Does It Matter?​

  • βœ… Speeds up repeated queries – data is read from local SSD instead of cloud storage.
  • βœ… Reduces cloud I/O costs – fewer trips to object storage.
  • βœ… Improves concurrency – multiple queries on the same warehouse benefit from cached blocks.
  • βœ… Optimizes long-running ETL – transformations that revisit data blocks reuse cache.

⚑ But unlike Result Cache (24h) and Metadata Cache (permanent), this cache is temporary and tied to the warehouse session.


πŸ”Ή Example Scenario​

Table: CUSTOMER_TRANSACTIONS​

CREATE OR REPLACE TABLE CUSTOMER_TRANSACTIONS (
ID NUMBER,
CUSTOMER_ID NUMBER,
AMOUNT NUMBER,
TRANS_DATE DATE
);

Suppose the table has 100 million rows.


Query 1 – First Run​

SELECT SUM(AMOUNT)
FROM CUSTOMER_TRANSACTIONS
WHERE TRANS_DATE BETWEEN '2025-09-01' AND '2025-09-10';
  • Warehouse reads required partitions from cloud storage.
  • These data blocks are now cached locally on SSD in the warehouse.

Query 2 – Second Run (same warehouse, still active)​

SELECT AVG(AMOUNT)
FROM CUSTOMER_TRANSACTIONS
WHERE TRANS_DATE BETWEEN '2025-09-01' AND '2025-09-10';
  • Snowflake detects the required blocks are already in local disk cache.
  • Query reads from SSD (much faster than pulling from cloud).
  • Still needs compute to aggregate, but avoids storage I/O.

πŸ”Ή When Local Disk Cache Helps Most​

  • Repeated queries on the same warehouse.
  • Workloads where queries access overlapping data ranges.
  • ETL pipelines where different stages touch the same source tables.
  • BI dashboards refreshed multiple times on the same warehouse.

πŸ”Ή Local Disk Cache vs Other Caches​

FeatureLocal Disk Cache (Warehouse SSD)Metadata Cache (Services Layer)Result Cache (Final Results)
StoresRaw data blocksMicro-partition stats (min/max, counts)Final query output
ScopePer warehouse nodeGlobal (all sessions/users)Global (same SQL, same role)
PersistenceLost when warehouse suspendsPermanent (until data changes)24 hours only
Use CaseSpeed up re-reads of same dataPartition pruning, optimize scansRe-run identical queries instantly
Cost ImpactReduces storage I/OReduces scanned partitions (less compute)Skips compute entirely (0 credits)

πŸ”Ή Limitations​

❌ Warehouse-bound – cache is not shared across different warehouses. ❌ Lost on suspend – if a warehouse is suspended or resized, cache is cleared. ❌ Still needs compute – unlike result cache, queries still consume credits. ❌ Data freshness – cache may become stale if underlying table is updated (Snowflake automatically refreshes as needed).


πŸ”Ή Best Practices​

  • βœ… Use multi-step ETL in the same warehouse session so later steps benefit from cached blocks.
  • βœ… For BI dashboards, keep warehouses warm (not suspended) if cache benefits are critical.
  • βœ… Avoid unnecessary warehouse suspensions between frequently repeated queries.
  • βœ… Size warehouses appropriately: bigger warehouses = bigger local cache.

πŸ”Ή Visual Flow (Local Disk Cache in Action)​


Can We Control Snowflake Caches?

πŸ”Ή 1. Result Cache (24h)​

  • ❌ You cannot extend or configure its 24-hour lifespan.
  • ❌ You cannot β€œpre-load” results.
  • βœ… You can disable it per session/query:
ALTER SESSION SET USE_CACHED_RESULT = FALSE;

or

SELECT COUNT(*) 
FROM SALES_TRANSACTIONS
WHERE TRANS_DATE = '2025-09-01'
-- Disables result cache for this query
-- and forces fresh execution
;

πŸ“Œ Use Case: Disable when you want fresh data after recent loads/updates.


πŸ”Ή 2. Metadata Cache​

  • ❌ You cannot disable or manually refresh this cache.
  • βœ… Snowflake automatically manages it.
    • When data changes (new micro-partitions, updates, deletes), Snowflake refreshes metadata instantly.
    • As a user, you only benefit from it, no control knobs available.

πŸ”Ή 3. Local Disk Cache (Warehouse SSD)​

  • ❌ You cannot directly enable/disable or extend it.

  • βœ… Cache automatically warms up when a warehouse reads data.

  • ❌ Cache is cleared when:

    • Warehouse is suspended.
    • Warehouse is resized.
    • Data changes in storage (Snowflake invalidates stale blocks).
  • βœ… Indirect control:

    • Keep warehouse running to preserve cache.
    • Use larger warehouses (more nodes β†’ more SSD cache capacity).
    • Run pipelines in the same warehouse session to reuse cached blocks.

πŸ”Ή Summary

Cache TypeCan Enable/Disable?Can Extend/Control?Notes
Result Cacheβœ… Disable (USE_CACHED_RESULT=FALSE)❌ Cannot extend beyond 24hGood for repeated queries, 0 credits
Metadata Cache❌ No❌ NoFully managed by Snowflake
Local Disk Cache❌ No❌ No (but indirectly via warehouse size/usage)Lost on suspend/resize

  • βœ… In short:
    • Local Disk Cache = the warehouse’s short-term memory. It avoids repeatedly pulling data from cloud storage, which improves speed and reduces I/O costs. But it’s temporary, tied to the warehouse, and disappears when the warehouse suspends.

    • Snowflake fully manages caching. You can only disable result cache if you want fresh data; everything else (metadata cache & local disk cache) is automatic and outside user control.